package com.icss.dao;

import com.icss.domian.QueryVO;
import com.icss.domian.VehicleIllegalRecord;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface IRecordDao extends JpaRepository<VehicleIllegalRecord,Integer>,
        JpaSpecificationExecutor<VehicleIllegalRecord> {

    @Query(value = "select r.illegal_record_id as illegalRecordId, r.illegal_record_time  as illegalRecordTime, r.illegal_record_license as illegalRecordLicense," +
            "u.user_name as userName,u.phone,i.illegal_item_code as illegalItemCode,i.illegal_item_name as illegalItemName," +
            "r.illegal_record_address as illegalRecordAddress,i.illegal_item_fine as illegalItemFine," +
            "i.illegal_item_score as illegalItemScore,r.illegal_record_status as illegalRecordStatus " +
            "from t_vehicle_illegal_record as r,t_user as u,t_illegal_item as i " +
            "where r.illegal_item_id = i.illegal_item_id and r.user_id=u.user_id order by illegalRecordId ", nativeQuery = true)
    List<QueryVO> findQueryVO();


    @Query(value = "select r.illegal_record_id as illegalRecordId, r.illegal_record_time  as illegalRecordTime, r.illegal_record_license as illegalRecordLicense," +
            "u.user_name as userName,u.phone,i.illegal_item_code as illegalItemCode,i.illegal_item_name as illegalItemName," +
            "r.illegal_record_address as illegalRecordAddress,i.illegal_item_fine as illegalItemFine," +
            "i.illegal_item_score as illegalItemScore,r.illegal_record_status as illegalRecordStatus " +
            "from t_vehicle_illegal_record  r,t_user  u,t_illegal_item  i " +
            "where r.illegal_item_id = i.illegal_item_id and r.user_id=u.user_id order by illegalRecordId ", nativeQuery = true)
    Page<QueryVO> findPage(Pageable pageable);
//    手写分页
    @Query(value = "select r.illegal_record_id as illegalRecordId, r.illegal_record_time illegalRecordTime, r.illegal_record_license illegalRecordLicense," +
            "u.user_name userName,u.phone,i.illegal_item_code illegalItemCode,i.illegal_item_name illegalItemName," +
            "r.illegal_record_address illegalRecordAddress,i.illegal_item_fine illegalItemFine," +
            "i.illegal_item_score illegalItemScore,r.illegal_record_status illegalRecordStatus " +
            "from t_vehicle_illegal_record  r,t_user   u,t_illegal_item   i " +
            "where r.illegal_item_id = i.illegal_item_id and r.user_id=u.user_id order by illegalRecordId limit ?1,?2", nativeQuery = true)
    List<QueryVO> findPage1(int startIndex, int count);


    @Query(value = "select  count(*) from t_vehicle_illegal_record " ,nativeQuery = true)
    Integer countAllPage();


//  完成按车牌号模糊查询，完成按违法开始时间和违法结束时间执行时间区间查询功能

    @Query(value = "select r.illegal_record_id as illegalRecordId, r.illegal_record_time illegalRecordTime, r.illegal_record_license illegalRecordLicense," +
            "            u.user_name userName,u.phone,i.illegal_item_code illegalItemCode,i.illegal_item_name illegalItemName," +
            "            r.illegal_record_address illegalRecordAddress,i.illegal_item_fine illegalItemFine," +
            "            i.illegal_item_score illegalItemScore,r.illegal_record_status illegalRecordStatus " +
            "            from t_vehicle_illegal_record as  r,t_user as  u,t_illegal_item as  i " +
            "            where r.illegal_item_id = i.illegal_item_id and r.user_id=u.user_id and r.illegal_record_time> ?1 " +
            "and r.illegal_record_time< ?2 and  r.illegal_record_license= ?3 order by illegalRecordId " ,nativeQuery = true)
    List<QueryVO> findByTimeAndCordLicense(String beginTime,String endTime,String RecordLicense);

    @Modifying
    @Query(value = " insert into t_vehicle_illegal_record(user_id,illegal_item_id,illegal_record_time,illegal_record_license,illegal_record_address) values (?1,?2,?3,?4,?5)" ,nativeQuery = true)
    void  updateIllegalItem(Integer userId, Integer illegalItemId, String illegalRecordTime, String illegalRecordLicense, String illegalRecordAddress);

    @Modifying
    @Query(value = " update t_vehicle_illegal_record set illegal_record_license=?2 where user_id=?1 " ,nativeQuery = true)
    void  updateRecordLicenseByUserId(Integer userId, String illegalRecordLicense);

    @Transactional
    @Modifying
    @Query(value = " update t_vehicle_illegal_record set illegal_record_status = '1' where illegal_record_id =?1 ", nativeQuery = true)
    void  updateIllegalRecordStatusById(Integer illegalRecordId);

}
